﻿<%@ WebHandler Language="C#" Class="ContainerShipPlanTrendDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
using System.Collections;

/**
 * Description: 转工排产趋势统计看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2022.11.1
 */

public class ContainerShipPlanTrendDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getFromFactoryData":
                        r = getFromFactoryData(jObject);//机加分厂数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getJiJiaDetail":
                        r = getJiJiaDetail(jObject);//机加分厂明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getToFactoryData":
                        r = getToFactoryData(jObject);//热表分厂数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getRbDetail":
                        r = getRbDetail(jObject);//热表分厂明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportJijiaDetail":
                        exportJijiaDetail(jObject);//导出机加明细
                        break;
                    case "exportRbDetail":
                        exportRbDetail(jObject);//导出热表明细
                        break;
                    case "cacheJiJiaData":
                        r = cacheJiJiaData(jObject);//缓存机加缓存
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "cacheRbData":
                        r = cacheRbData(jObject);//缓存热表缓存
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //统计机加分厂数据
    string getFromFactoryDataSql(JObject jObject)
    {
        string returnSql = "";
        string sql = @"
SELECT  DISTINCT u.attributevalue factorycode,f.factoryname,cci.containername,ws.workflowstepname,csp.plannedstartdate,csd.toacceptdate,
        CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) ELSE 0 END sendoutdelay,
        '{2}' selectdate
FROM (SELECT * FROM containerstepplan csp WHERE csp.plantype = 2 AND TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(csp.plannedstartdate) <= TO_DATE('{1}','yyyy-mm-dd')) csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csp.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid --批次所属分厂
LEFT JOIN factory f2 ON f2.factoryid = s.factoryid --工序所属分厂
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
LEFT JOIN Userattribute u2 ON u2.parentid = f2.factoryid AND u2.userattributename = '分厂代码'
LEFT JOIN mesreport_containership_table sp ON sp.containerid = csp.containerid AND sp.workflowstepname = ws.workflowstepname
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = sp.containershiprecordid
WHERE cci.containerstatus = 1
";
        //承制分厂
        if (jObject["tofactoryCode"] != null && jObject["tofactoryCode"].ToString() != "")
        {
            sql += string.Format(" AND u2.attributevalue = '{0}'", jObject["tofactoryCode"].ToString());
        }

        //主制分厂
        if (jObject["fromfactoryCodeList"] != null)
        {
            JArray arrFromfactoryCode = JArray.Parse(jObject["fromfactoryCodeList"].ToString());
            string strFromfactoryCodeList = "";
            for (int i = 0; i < arrFromfactoryCode.Count; i++)
            {
                strFromfactoryCodeList += string.Format("'{0}',", arrFromfactoryCode[i].ToString());
            }
            strFromfactoryCodeList = strFromfactoryCodeList.TrimEnd(',');
            sql += string.Format(" AND u.attributevalue IN ({0})", strFromfactoryCodeList);
        }

        if (jObject["selectdate"] != null)
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            for (int i = 0; i < selectdate.Count; i++)
            {
                string tem = sql;
                string start = selectdate[i]["value"].ToString().Split('~')[0];
                string end = selectdate[i]["value"].ToString().Split('~')[1];
                tem = string.Format(sql, start, end, selectdate[i]["value"].ToString());
                returnSql += string.Format(" UNION ALL {0}", tem);
            }
            returnSql = returnSql.Substring(11, returnSql.Length - 11);
        }
        return returnSql;
    }

    //统计承制分厂数据
    string getToFactoryDataSql(JObject jObject, bool isSendOutNoDelayFilter = true)
    {
        string returnSql = "";
        string sql = @"
SELECT DISTINCT u.attributevalue fromfactorycode,f.factoryname fromfactoryname,u2.attributevalue tofactorycode,f2.factoryname tofactoryname,
       cci.containername,ws.workflowstepname,csp.plannedfinishdate,csr.recivedate,
       CASE WHEN csp.plannedfinishdate IS NOT NULL 
            THEN CASE WHEN csr.recivedate IS NOT NULL THEN TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate)
                  ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate)
                 END 
            ELSE 0 
       END finishdelaydays,
       '{2}' selectdate
FROM (SELECT * FROM containerstepplan csp WHERE csp.plantype = 2 AND TRUNC(csp.plannedfinishdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(csp.plannedfinishdate) <= TO_DATE('{1}','yyyy-mm-dd')) csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csp.workflowstepid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN factory f2 ON f2.factoryid = s.factoryid
LEFT JOIN Userattribute u2 ON u2.parentid = f2.factoryid AND u2.userattributename = '分厂代码'
LEFT JOIN mesreport_containership_table sp ON sp.containerid = csp.containerid AND sp.workflowstepname = ws.workflowstepname
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = sp.containershiprecordid AND csd.isuse = 0
LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid AND csd.isuse = 0
WHERE cci.containerstatus = 1 AND (TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = 0 OR TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = -1)
";
        //承制分厂
        if (jObject["tofactoryCode"] != null && jObject["tofactoryCode"].ToString() != "")
        {
            sql += string.Format(" AND u2.attributevalue = '{0}'", jObject["tofactoryCode"].ToString());
        }

        //主制分厂
        if (jObject["fromfactoryCodeList"] != null)
        {
            JArray arrFromfactoryCode = JArray.Parse(jObject["fromfactoryCodeList"].ToString());
            string strFromfactoryCodeList = "";
            for (int i = 0; i < arrFromfactoryCode.Count; i++)
            {
                strFromfactoryCodeList += string.Format("'{0}',", arrFromfactoryCode[i].ToString());
            }
            strFromfactoryCodeList = strFromfactoryCodeList.TrimEnd(',');
            sql += string.Format(" AND u.attributevalue IN ({0})", strFromfactoryCodeList);
        }

        if (jObject["selectdate"] != null)
        {
            JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
            for (int i = 0; i < selectdate.Count; i++)
            {
                string tem = sql;
                string start = selectdate[i]["value"].ToString().Split('~')[0];
                string end = selectdate[i]["value"].ToString().Split('~')[1];
                tem = string.Format(sql, start, end, selectdate[i]["value"].ToString());
                returnSql += string.Format(" UNION ALL {0}", tem);
            }
            returnSql = returnSql.Substring(11, returnSql.Length - 11);
        }
        return returnSql;
    }

    //主制分厂
    Result getFromFactoryData(JObject jObject)
    {
        Result r = new Result();
        //先查询缓存数据
        //        string unionSql = "";
        //        string sql = @"
        //SELECT mt.selectdate,mt.fromfactoryname factoryname,u.attributevalue factorycode,mt.sendoutplanqty,mt.sendoutintimeqty,mt.sendoutintimerate
        //FROM mesreport_shipplantrend_jj mt
        //LEFT JOIN factory f ON f.factoryname = mt.fromfactoryname
        //LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
        //WHERE mt.selectdate = '{0}' AND mt.tofactoryname = '{1}'";

        //        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        //        for (int i = 0; i < selectdate.Count; i++)
        //        {
        //            string temp = string.Format(sql, selectdate[i]["value"], jObject["tofactoryname"].ToString());
        //            unionSql += string.Format(" UNION ALL {0}", temp);
        //        }
        //        unionSql = unionSql.Substring(11, unionSql.Length - 11);

        //        DataTable dtCache = dal.GetDataTable(unionSql);
        //        if (dtCache.Rows.Count > 0)
        //        {
        //            foreach (DataColumn col in dtCache.Columns)
        //            {
        //                col.ColumnName = col.ColumnName.ToLower();
        //            }
        //            r.data = dtCache;
        //            r.total = Convert.ToInt32(dtCache.Rows.Count);
        //            r.meta = new Result.Meta(200, "请求成功");
        //            return r;
        //        }

        string sql = @"
SELECT  t.factoryname,t.factorycode,t.selectdate,COUNT(1) sendoutPlanqty,--计划发出数
        SUM(CASE WHEN  t.toacceptdate IS NOT NULL AND (t.sendoutdelay = '0' OR t.sendoutdelay = '-1') THEN 1 ELSE 0 END) sendoutIntimeQty,--准时发出数
        ROUND(SUM(CASE WHEN t.toacceptdate IS NOT NULL AND (t.sendoutdelay = '0' OR t.sendoutdelay = '-1') THEN 1 ELSE 0 END) / COUNT(1),4) * 100 sendoutIntimeRate--准时发出率
FROM (
    {0}
) t
GROUP BY t.factorycode,t.selectdate,t.factoryname
ORDER BY t.factorycode,t.selectdate,t.factoryname
";
        sql = string.Format(sql, getFromFactoryDataSql(jObject));

        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //承制分厂
    Result getToFactoryData(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT  t.selectdate,COUNT(1) finishPlanqty,
        SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END) finishIntimeQty,
        ROUND(SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END) / COUNT(1),4) * 100 finishIntimeRate
FROM (
    {0}
) t
GROUP BY selectdate
ORDER BY selectdate
";
        sql = string.Format(sql, getToFactoryDataSql(jObject));

        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //统计机加分厂数据
    string getJiJiaSql(JObject jObject)
    {
        string sql = @"
SELECT  DISTINCT f.factoryname,cci.fromworkcentername,cci.productname,cci.productdescription,cci.containername,
        cci.workflowstepname currentspec,
        CASE WHEN csr.recivedate IS NOT NULL THEN (csr.acceptanceqty + csr.lossqty) ELSE cci.qty END qty,
        ws.workflowstepname,
        e.fullname receiver,csd.toacceptdate recivedate,
        CASE WHEN pdr.parentid IS NOT NULL THEN '转工拒收'
            WHEN pdr.parentid IS NULL AND csd.senddate IS NULL THEN '未转出'
            WHEN pdr.parentid IS NULL AND csd.senddate IS NOT NULL AND csd.toacceptdate IS NULL THEN '待接收'  
            WHEN pdr.parentid IS NULL AND csd.toacceptdate IS NOT NULL AND csr.completedate IS NULL THEN '待完成'  
            WHEN pdr.parentid IS NULL AND csr.completedate IS NOT NULL AND csr.recivedate IS NULL THEN '待转入'
            WHEN pdr.parentid IS NULL AND csr.recivedate IS NOT NULL THEN '已转入' 
        END status,
        csp.stepplancreatedate,
        csd.senddate,
        csp.stepplanshipdate,jje.fullname jjplaner,
        CASE WHEN csp.containerstepplanid IS NOT NULL THEN '已排产' ELSE '未排产' END planstatus,
        rbe.fullname rbplaner,
        csp.plannedstartdate,
        csd.needcompletedate,
        csd.estimatedcompletedate,
        csr.completedate,
        csr.recivedate shipindate,
        pdr.createdate denydate,de.fullname denyer,pdr.reasonnote denyreason,
        CASE WHEN pdr.parentid IS NOT NULL THEN 0
             WHEN pdr.parentid IS NULL AND csd.needcompletedate IS NULL THEN 0
             WHEN pdr.parentid IS NULL AND csd.needcompletedate IS NOT NULL AND csr.completedate IS NULL THEN TRUNC(SYSDATE) - TRUNC(csd.needcompletedate)
             ELSE TRUNC(csr.completedate) - TRUNC(csd.needcompletedate)
        END delay,
        CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate)
             ELSE 0
        END sendoutdelay,
        CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csp.plannedstartdate) -  TRUNC(csd.toacceptdate)
             ELSE 0 
        END presend,
        csp.plannedfinishdate
FROM containerstepplan csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csp.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid --批次所属分厂
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
LEFT JOIN factory f2 ON f2.factoryid = s.factoryid --工序所属分厂
LEFT JOIN Userattribute u2 ON u2.parentid = f2.factoryid AND u2.userattributename = '分厂代码'
LEFT JOIN mesreport_containership_table sp ON sp.containerid = csp.containerid AND sp.workflowstepname = ws.workflowstepname
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = sp.containershiprecordid
LEFT JOIN containershiprecive csr ON csr.containershiprecordid = sp.containershiprecordid
LEFT JOIN processdealrecord pdr ON pdr.parentid = sp.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4) --转工拒收  
LEFT JOIN employee e ON e.employeeid = csd.toacceptor --热表接收人
LEFT JOIN employee de ON de.employeeid = pdr.employeeid --转工拒收人
LEFT JOIN employee jje ON jje.employeeid = csp.stepplancreateemployeeid --机加排产人
LEFT JOIN employee rbe ON rbe.employeeid = csp.stepplanshipemployeeid --热表排产人
WHERE csp.plantype = 2 AND cci.containerstatus = 1 
";
        //主制分厂
        if (jObject["fromfactoryCode"] != null && jObject["fromfactoryCode"].ToString() != "")
        {
            sql += string.Format(" AND u.attributevalue = '{0}'", jObject["fromfactoryCode"].ToString());
        }

        //承制分厂
        if (jObject["tofactoryCode"] != null && jObject["tofactoryCode"].ToString() != "")
        {
            sql += string.Format(" AND u2.attributevalue = '{0}'", jObject["tofactoryCode"].ToString());
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            string start = jObject["selectdate"].ToString().Split('~')[0];
            string end = jObject["selectdate"].ToString().Split('~')[1];
            sql += string.Format(@" AND TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(csp.plannedstartdate) <= TO_DATE('{1}','yyyy-mm-dd')", start, end);
        }
        return sql;
    }

    //统计承制分厂数据
    string getRbSqlString(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname fromfactory,f2.factoryname tofactory,w.workcentername toworkcentername,cci.productname,cci.productdescription,
       cci.containername,cci.qty,ws.workflowstepname,cci.workflowstepname currentworkflowstepname,csp.stepplancreatedate,
       csp.plannedstartdate,csp.plannedfinishdate,csd.senddate,csd.toacceptdate,e.fullname receiver,csr.completedate,csr.recivedate,
       CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate)
            ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedstartdate)
       END senddelaydays,
       CASE WHEN csp.plannedfinishdate IS NOT NULL 
            THEN CASE WHEN csr.recivedate IS NOT NULL THEN TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate)
                  ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate)
                 END 
            ELSE 0 
       END finishdelaydays,
       CASE WHEN dt.createdate IS NOT NULL THEN '已派工' ELSE '待派工' END isdispatch
FROM containerstepplan csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csp.workflowstepid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid --主制分厂
LEFT JOIN workflowstep wss ON wss.workflowstepid = csp.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = wss.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wss.specid)
LEFT JOIN factory f2 ON f2.factoryid = s.factoryid --工序分厂
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
LEFT JOIN Userattribute u2 ON u2.parentid = f2.factoryid AND u2.userattributename = '分厂代码'
LEFT JOIN mesreport_containership_table sp ON sp.containerid = csp.containerid AND sp.workflowstepname = ws.workflowstepname
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = sp.containershiprecordid AND csd.isuse = 0 
LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid AND csd.isuse = 0
LEFT JOIN workcenter w ON w.workcenterid = csd.toworkcenterid
LEFT JOIN employee e ON e.employeeid = csd.ToAcceptor --热表接收人
LEFT JOIN containerstepdispatch dt ON dt.containerid = csp.containerid AND dt.workflowstepid = csp.workflowstepid AND dt.isuse = 0 
WHERE csp.plantype = 2 AND cci.containerstatus = 1 AND (TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = 0 OR TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = -1)
";
        //主制分厂
        if (jObject["fromfactoryCodeList"] != null)
        {
            JArray arrFromfactoryCode = JArray.Parse(jObject["fromfactoryCodeList"].ToString());
            string strFromfactoryCodeList = "";
            for (int i = 0; i < arrFromfactoryCode.Count; i++)
            {
                strFromfactoryCodeList += string.Format("'{0}',", arrFromfactoryCode[i].ToString());
            }
            strFromfactoryCodeList = strFromfactoryCodeList.TrimEnd(',');
            sql += string.Format(" AND u.attributevalue IN ({0})", strFromfactoryCodeList);
        }

        //承制分厂
        if (jObject["tofactoryCode"] != null && jObject["tofactoryCode"].ToString() != "")
        {
            sql += string.Format(" AND u2.attributevalue = '{0}'", jObject["tofactoryCode"].ToString());
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            string start = jObject["selectdate"].ToString().Split('~')[0];
            string end = jObject["selectdate"].ToString().Split('~')[1];
            sql += string.Format(@" AND TRUNC(csp.plannedfinishdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(csp.plannedfinishdate) <= TO_DATE('{1}','yyyy-mm-dd')", start, end);
        }
        return sql;
    }

    //机加分厂明细数据
    Result getJiJiaDetail(JObject jObject)
    {
        string sql = @" SELECT  t.* FROM ( {0} ) t";
        sql = string.Format(sql, getJiJiaSql(jObject));
        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() == "准时发出数")
        {
            sql += " WHERE t.recivedate IS NOT NULL AND (t.sendoutdelay = '0' OR t.sendoutdelay = '-1')";
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //热表分厂明细数据
    Result getRbDetail(JObject jObject)
    {
        string sql = @" SELECT  t.* FROM ( {0} ) t";
        sql = string.Format(sql, getRbSqlString(jObject));

        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() == "准时完成数")
        {
            sql += " WHERE t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 ";
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出机加分厂明细数据
    void exportJijiaDetail(JObject jObject)
    {
        string sql = @" SELECT  t.* FROM ( {0} ) t";
        sql = string.Format(sql, getJiJiaSql(jObject));
        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() == "准时发出数")
        {
            sql += " WHERE t.recivedate IS NOT NULL AND (t.sendoutdelay = '0' OR t.sendoutdelay = '-1')";
        }

        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns["currentspec"].Caption = "当前工序";
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["workflowstepname"].Caption = "转工工序";
        dtResult.Columns["receiver"].Caption = "接收人";
        dtResult.Columns["recivedate"].Caption = "接收时间";
        dtResult.Columns["status"].Caption = "转工状态";
        dtResult.Columns["stepplancreatedate"].Caption = "机加排产时间";
        dtResult.Columns["senddate"].Caption = "实际发出时间";
        dtResult.Columns["stepplanshipdate"].Caption = "承制分厂排产时间";
        dtResult.Columns["jjplaner"].Caption = "机加排产人";
        dtResult.Columns["planstatus"].Caption = "排产状态";
        dtResult.Columns["rbplaner"].Caption = "承制分厂排产人";
        dtResult.Columns["plannedstartdate"].Caption = "计划发出时间";
        dtResult.Columns["needcompletedate"].Caption = "要求完成时间";
        dtResult.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dtResult.Columns["completedate"].Caption = "完成时间";
        dtResult.Columns["shipindate"].Caption = "转入时间";
        dtResult.Columns["denydate"].Caption = "拒收时间";
        dtResult.Columns["denyer"].Caption = "拒收人";
        dtResult.Columns["denyreason"].Caption = "拒收原因";
        dtResult.Columns["delay"].Caption = "完成超期时间";
        dtResult.Columns["sendoutdelay"].Caption = "发出超期时间";
        dtResult.Columns["presend"].Caption = "发出提前时间";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["fromworkcentername"].Caption = "责任工区";
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }

    //导出热表分厂明细数据
    void exportRbDetail(JObject jObject)
    {
        string sql = @" SELECT  t.* FROM ( {0} ) t";
        sql = string.Format(sql, getRbSqlString(jObject));

        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() == "准时完成数")
        {
            sql += " WHERE t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 ";
        }

        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns.Remove("isdispatch");
        dtResult.Columns["fromfactory"].Caption = "主制分厂";
        dtResult.Columns["tofactory"].Caption = "承制分厂";
        dtResult.Columns["toworkcentername"].Caption = "承制工区";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["workflowstepname"].Caption = "排产工序";
        dtResult.Columns["plannedstartdate"].Caption = "plannedstartdate";
        dtResult.Columns["senddate"].Caption = "实际发出时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        dtResult.Columns["receiver"].Caption = "接收人";
        dtResult.Columns["completedate"].Caption = "实际完成时间";
        dtResult.Columns["recivedate"].Caption = "转入时间";
        dtResult.Columns["currentworkflowstepname"].Caption = "当前工序";
        dtResult.Columns["finishdelaydays"].Caption = "完成超期时间";
        dtResult.Columns["plannedstartdate"].Caption = "计划发出时间";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["stepplancreatedate"].Caption = "排产日期";
        dtResult.Columns["senddelaydays"].Caption = "发出超期时间";
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString().ToString());
    }

    //缓存机加数据
    Result cacheJiJiaData(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT  t.factoryname,t.factorycode,t.selectdate,COUNT(1) sendoutPlanqty,--计划发出数
        SUM(CASE WHEN  t.toacceptdate IS NOT NULL AND (t.sendoutdelay = '0' OR t.sendoutdelay = '-1') THEN 1 ELSE 0 END) sendoutIntimeQty,--准时发出数
        ROUND(SUM(CASE WHEN t.toacceptdate IS NOT NULL AND (t.sendoutdelay = '0' OR t.sendoutdelay = '-1') THEN 1 ELSE 0 END) / COUNT(1),4) * 100 sendoutIntimeRate--准时发出率
FROM (
    {0}
) t
GROUP BY t.factorycode,t.selectdate,t.factoryname
ORDER BY t.factorycode,t.selectdate,t.factoryname
";
        DataTable dtResult = dal.GetDataTable(string.Format(sql, getFromFactoryDataSql(jObject)));
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtResult.Rows)
        {
            sql = string.Format("DELETE mesreport_shipplantrend_jj t WHERE t.selectdate = '{0}' AND t.tofactoryname = '{1}'", dr["selectdate"], jObject["tofactoryname"].ToString());
            sqlList.Add(sql);
        }

        //删除缓存
        dal.ExecuteSqlTran(sqlList);

        sqlList = new ArrayList();
        foreach (DataRow dr in dtResult.Rows)
        {
            sql = @"
INSERT INTO mesreport_shipplantrend_jj(
selectdate,fromfactoryname,tofactoryname,sendoutplanqty,sendoutintimeqty,sendoutintimerate) 
VALUES('{0}', '{1}','{2}','{3}','{4}','{5}')
";
            sql = string.Format(sql,
                                dr["selectdate"],//筛选时间
                                dr["factoryname"],//机加分厂
                                dr["tofactoryname"].ToString(),//承制分厂
                                dr["sendoutplanqty"].ToString(),//计划发出数
                                dr["sendoutintimeqty"].ToString(),//准时发出数
                                dr["sendoutintimerate"].ToString());//准时发出率
            sqlList.Add(sql);
        }

        try
        {
            //插入缓存
            dal.ExecuteSqlTran(sqlList);
            r.data = null;
            r.total = Convert.ToInt32(dtResult.Rows.Count);
            r.meta = new Result.Meta(200, "保存成功");
            return r;
        }
        catch (Exception e)
        {
            r.data = null;
            r.total = Convert.ToInt32(dtResult.Rows.Count);
            r.meta = new Result.Meta(500, e.Message);
            return r;
        }
    }

    //缓存热表缓存
    Result cacheRbData(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT  t.selectdate,t.fromfactoryname,t.tofactoryname,
        COUNT(1) finishPlanqty,
        SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END) finishIntimeQty,
        ROUND(SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END) / COUNT(1),4) * 100 finishIntimeRate
FROM (
    {0}
) t
GROUP BY t.selectdate,t.fromfactoryname,t.tofactoryname
";
        DataTable dtResult = dal.GetDataTable(string.Format(sql, getToFactoryDataSql(jObject)));
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        ArrayList sqlList = new ArrayList();
        foreach (DataRow dr in dtResult.Rows)
        {
            sql = string.Format("DELETE mesreport_shipplantrend_rb t WHERE t.selectdate = '{0}' AND t.tofactoryname = '{1}'", dr["selectdate"], jObject["tofactoryname"].ToString());
            sqlList.Add(sql);
        }

        //删除缓存
        dal.ExecuteSqlTran(sqlList);

        sqlList = new ArrayList();
        foreach (DataRow dr in dtResult.Rows)
        {
            sql = @"
INSERT INTO mesreport_shipplantrend_rb(
selectdate,fromfactoryname,tofactoryname,finishPlanqty,finishIntimeQty,finishIntimeRate) 
VALUES('{0}', '{1}','{2}','{3}','{4}','{5}')
";
            sql = string.Format(sql,
                                dr["selectdate"],//筛选时间
                                dr["fromfactoryname"],//机加分厂
                                dr["tofactoryname"].ToString(),//承制分厂
                                dr["finishPlanqty"].ToString(),//计划完成数
                                dr["finishIntimeQty"].ToString(),//准时完成数
                                dr["finishIntimeRate"].ToString());//准时完成率
            sqlList.Add(sql);
        }

        try
        {
            //插入缓存
            dal.ExecuteSqlTran(sqlList);
            r.data = null;
            r.total = Convert.ToInt32(dtResult.Rows.Count);
            r.meta = new Result.Meta(200, "保存成功");
            return r;
        }
        catch (Exception e)
        {
            r.data = null;
            r.total = Convert.ToInt32(dtResult.Rows.Count);
            r.meta = new Result.Meta(500, e.Message);
            return r;
        }
    }
}